USE [comunion]
GO
/****** Object:  StoredProcedure [dbo].[rsp_Transaction_Voucher_Details]    Script Date: 08/11/2010 15:23:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--EXEC rsp_Transaction_Voucher_Details 'VC-1000001','ASC'
ALTER PROCEDURE [dbo].[rsp_Transaction_Voucher_Details]
	@cTranNo VARCHAR(25),
	@cCompID VARCHAR(10)
AS 
declare @retstr varchar(8000)

SELECT * INTO ##table FROM (
			SELECT distinct a.cTranNo, a.cType, b.cPINo AS cAcctno, b.cPINo AS cDesc, b.nAmount, b.nApplied, b.nEWT FROM voucher a
			LEFT OUTER JOIN voucher_trade b ON b.cCompanyID = a.cCompanyID AND b.cTranNo = a.cTranNo
			WHERE a.cType = 'Trade' AND a.cTranNo = @cTranNo AND a.cCompanyID =  @cCompID

			UNION ALL

			SELECT distinct a.cTranNo, a.cType, b.cPayableID AS cAcctno, b.cDesc, b.nAmount, b.nApplied, b.nEWT FROM voucher a
			LEFT OUTER JOIN voucher_nontrade b ON b.cCompanyID = a.cCompanyID AND b.cTranNo = a.cTranNo
			WHERE a.cType = 'Non-Trade' AND a.cTranNo = @cTranNo AND a.cCompanyID =  @cCompID

			UNION ALL

			SELECT distinct a.cTranNo, a.cType, b.cAcctNo, b.cDesc, b.nDebit AS nAmount, b.nDebit AS nApplied, 0 as nEWT FROM voucher a
			LEFT OUTER JOIN voucher_opex b ON b.cCompanyID = a.cCompanyID AND b.cTranNo = a.cTranNo
			WHERE a.cType = 'Others' AND a.cTranNo = @cTranNo AND a.cCompanyID =  @cCompID) A


			SELECT @retstr =  COALESCE(@retstr + ';','') + cAcctno FROM ##table
			SELECT @retstr AS cAcctno  INTO ##table2
			SELECT cTranNo,cType,sum(nEWT) as nEWT,B.cAcctno, sum(nAmount) AS nAmount from ##table A
			left join(
				select * from ##table2
				)B on A.cTranNo <> B.cAcctno GROUP BY A.ctranno, A.cType, B.cAcctno

			DROP table ##table
			DROP TABLE ##table2

